import json
import os
import threading

import pymysql
import pymssql
from DBUtils.PooledDB import PooledDB
from pymysql.cursors import DictCursor
from pymysql.constants import CLIENT

from  apilib.component import compManager , TComp

class TCompDB( TComp):
    def __init__(self):
        super().__init__()
        self.handlers={
            "query": self.queryByKey,
            "createRow": self.createRow,
            "updateRow": self.updateRow,
            "createOrUpdateRow": self.createOrUpdateRow,
            "deleteRow": self.deleteRow,
            "viewRows": self.viewRows,
            "execProc": self.execProc,
            "execute": self.execute,
            "executeLock": self.executeLock,
            "executeAsync": self.executeAsync,
            "getBufferData": self.getBufferData
        }
        self.autoStartServices={}
        self.pool = None
        self.lockObj = threading.Lock()
        self.bufferData={}
    def compInit(self):
        try:
            pass
        except Exception as er:
            self.manager.log(er)
    def getPool(self):
        status = 0
        try:
            if ( self.pool == None):
                pool = None
                if self.compSettings['dbType'] == 'mysql':
                    pool , status = self.getMysqlPool( )
                elif self.compSettings['dbType'] == 'mssql':
                    pool ,status = self.getMssqlPool( )
                else:
                    status = 1002
                self.pool = pool
            else:
                status  = 1
        except Exception as er:
            print(er)
        return self.pool, status
    def getMysqlPool(self ):
        pool = None
        status = 0
        try:
            settingItem = self.compSettings
            pool = PooledDB(
                creator=pymysql,
                mincached=1,
                maxcached=100,
                host=settingItem["host"],
                port=settingItem["port"],
                database=settingItem["db"],
                user=settingItem["uid"],
                password=settingItem["pwd"],
                autocommit=True,
                cursorclass=DictCursor,
                client_flag=CLIENT.MULTI_STATEMENTS
            )
            status = 1
        except Exception as er:
            status = 1003
            print(er)
        return pool, status
    def getMssqlPool(self):
        pool = None
        status = 0
        try:
            settingItem = self.compSettings
            pool = PooledDB(
                creator=pymssql,
                maxconnections=200,
                mincached=10,
                maxcached=15,
                maxshared=0,
                maxusage=1000,
                ping=1,
                blocking=True,
                host=settingItem["host"],
                port=settingItem["port"],
                database=settingItem["db"],
                user=settingItem["uid"],
                password=settingItem["pwd"],
                autocommit=True,
                # closeable=False,
            )
            status = 1
        except Exception as er:
            status = 1003
            print(er)
        return pool, status
    def getConnection(self):
        conn = None
        status = 0
        try:
            pool, poolStatus = self.getPool()
            if poolStatus == 1:
                conn = pool.connection()
                status = 1
            else:
                status = poolStatus
        except Exception as er:
            print(er)
        return conn, status
    def query(self,  sql, params):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            conn, connstatus = self.getConnection( )
            if connstatus == 1:
                settingItem = self.compSettings
                dbType = settingItem['dbType']
                cursor = None
                if dbType == 'mysql':
                    cursor = conn.cursor()
                else:
                    cursor = conn.cursor(as_dict=True)
                try:
                    if params != None:
                        conn.cursorclass = pymysql.cursors.DictCursor
                        cursor.execute(sql, params)
                    else:
                        cursor.execute(sql)
                    if cursor.description != None:
                        rsList = []
                        rs = cursor.fetchall()
                        rsList.append(list(rs))
                        # queryRes['datasetList'].append(list(rs))
                        while cursor.nextset():
                            rs = cursor.fetchall()
                            rsList.append(list(rs))
                            # queryRes['datasetList'].append(list(rs))
                        queryRes['datasetList'] = rsList
                        if len(queryRes['datasetList']) > 0:
                            queryRes['recordset'] = queryRes['datasetList'][0]
                    else:
                        queryRes['executeResult'] = {
                            "rowNum": cursor.rownumber,
                            "rowCount": cursor.rowcount,
                            "newId": cursor.lastrowid
                        }
                    queryRes['queryStatus'] = 1
                except Exception as eer:
                    print('query faild.', eer)
                    queryRes['queryStatus'] = 1004
                    queryRes['errMsg'] = eer;
                cursor.close()
                cursor = None
                conn.close()
                conn = None
            else:
                queryRes['queryStatus'] = connstatus
        except Exception as er:
            print(er)
        return queryRes
    def getSql(self ,  dbKey  ):
        sql=""
        status=0
        try:
            sqlPath = self.compSettings['sqlPath']
            sqlPath = sqlPath.replace("{rootPath}" , self.manager.rootPath)
            fnPath = os.path.join( sqlPath , dbKey)
            if  fnPath.find(".")<0:
                fnPath = fnPath +".txt"
            if os.path.exists(fnPath):
                sql = ""
                with open(fnPath , 'r' , encoding='utf-8') as f :
                    sql = f.read()
                status = 1
            else:
                status = - 1
        except Exception as er:
            print(er)
        return  sql , status
    '''
    扫描出SQL中的参数列表 
    '''
    def getSqlList(self , dbCode   ):
        res={}
        try:
            fnPath = os.path.join(self.settings[dbCode]['sqlPath'] , "**/*.txt")
            import glob
            import  re
            for fn in glob.glob(fnPath):
                with open(fn ,'r', encoding='utf-8') as f:
                    content = f.read()
                    params = list(set( re.findall(r'(?<=\%\().*?(?=\)s)',content)))
                    res[fn]=params
        except Exception as er:
            print(er)
        return  res
    def queryByKey(self, key, params):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            sql , status = self.getSql(key ) 
            if status>0 :
                queryRes = self.query(  sql, params)
            else:
                print('file not exists :', key)
                queryRes['queryStatus'] = 1005
                queryRes['errMsg'] = "key(" + key + ") is not exists."
        except Exception as er:
            print(er)
        return queryRes
    def createRow(self,  table, fieldValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            fields = ','.join([x for x in fieldValues.keys()])
            values = ",".join(["%(" + x + ")s" for x in fieldValues.keys()])
            sql = "insert into " + table + "(" + fields + ") values (" + values + ")"
            queryRes = self.query(sql, fieldValues)
        except Exception as er:
            print(er)
        return queryRes
    def updateRow(self, table, fieldValues, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            fields = ','.join([x + '=%(' + x + ')s' for x in fieldValues.keys()])
            keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
            sql = "update " + table + ' set ' + fields + ' where ' + keys
            ps = {}
            for k in fieldValues.keys():
                ps[k] = fieldValues[k]
            for k in keyValues.keys():
                ps[k] = keyValues[k]
            queryRes = self.query( sql, ps)
        except Exception as er:
            print(er)
        return queryRes
    def createOrUpdateRow(self,  table, fieldValues, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            checkResult = self.viewRows(  table , keyValues)
            if checkResult['queryStatus']==1 :
                if len(checkResult['recordset'])==0:
                    queryRes = self.createRow( table , fieldValues)
                else:
                    queryRes = self.updateRow( table , fieldValues , keyValues)
        except Exception as er:
            print(er)
        return queryRes
    def deleteRow(self,  table, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
            if keys =='' :
                keys= '1>0'
            sql = "delete from  " + table + ' where ' + keys
            queryRes = self.query( sql, keyValues)
        except Exception as er:
            print(er)
        return queryRes
    def viewRows(self,  view, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            sql = "select * from  " + view
            if keyValues!=None and len(keyValues.keys())>0:
                keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
                sql = sql  + ' where ' + keys
            queryRes = self.query( sql, keyValues)
        except Exception as er:
            print(er)
        return queryRes
    def execProc(self,  procName, keyValues={}):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
                dbType = self.compSettings['dbType']
                if dbType == "mssql":
                    ps = ",".join([f"%({x})s" for x in keyValues.keys()])
                    sql = f"exec {procName} {ps}"
                    queryRes = self.query( sql, keyValues)
                elif dbType == "mysql":
                    ps = ",".join([f"%({x})s" for x in keyValues.keys()])
                    sql = f"call {procName}({ps});"
                    queryRes = self.query( sql, keyValues)
        except Exception as er:
            print(er)
        return queryRes
    def execute(self , actionCode ='viewRows' , table="" , fieldValues={} , keyValues={}  ):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if actionCode=='viewRows':
                queryRes = self.viewRows(table , fieldValues)
            elif actionCode=='createRow':
                queryRes = self.createRow(table , fieldValues)
            elif actionCode=='updateRow':
                queryRes = self.updateRow(table , fieldValues, keyValues)
            elif actionCode=='deleteRow':
                queryRes = self.viewRows(table , fieldValues)
            elif actionCode=='execProc':
                queryRes = self.execProc(table , fieldValues)
            else:
                queryRes ={
                    "queryStatus": -1000,
                    "errMsg": "actionCode error.",
                }
        except Exception as er:
            print(er)
        return queryRes
    def executeLock(self , actionCode ='viewRows' , table="" , fieldValues={} , keyValues={}   ):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        self.lockObj.acquire_lock()
        try:
            queryRes =self.execute(actionCode , table , fieldValues , keyValues)
        except Exception as er:
            self.manager.log(er)
        self.lockObj.release_lock()
        return  queryRes
    def executeAsync(self , actionCode ='viewRows' , table="" , fieldValues={} , keyValues={}   ):
        res={
            "ac":"executeAsync"
        }
        try:
            threading.Thread(target=self.executeLock , daemon=True , kwargs={
                "actionCode":actionCode ,
                "table":table,
                "fieldValues":fieldValues,
                "keyValues":keyValues
            }).start()
        except Exception as er:
            self.manager.log(er)
        return res
    def getBufferData(self, bufferCode, action , key, keyValues , forceUpdate=0):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            updateFlag = False
            if (bufferCode not in self.bufferData.keys()) or (forceUpdate>0):
                updateFlag = True
            if   updateFlag :
                if action == 'query':
                    self.bufferData[bufferCode] = self.queryByKey(key , keyValues)
                elif action == 'execProc':
                    self.bufferData[bufferCode] = self.execProc(key , keyValues)
                elif action == 'viewRows':
                    self.bufferData[bufferCode] = self.viewRows(key , keyValues)
            if bufferCode in self.bufferData.keys():
                queryRes = self.bufferData[bufferCode]
            else:
                queryRes = {
                    "queryStatus": -1000,
                    "errMsg": ""
                }
        except Exception as er:
            print(er)
        return queryRes

_settings = {
    "dbType":"mssql",
    "host": "iot.zn-power.com",
    "port": 30106,
    "uid": "sa",
    "pwd": "Hhuc1115",
    "db": "znInspection",
    "sqlPath": "./sqls/db/zninspection"
}
compManager.registComponentType('TCompDB' , TCompDB , _settings)
